1. Field of the Invention
The present invention relates to a multi-tenant application. More particularly, the present invention relates to a method and apparatus for initializing a multi-tenant database.
2. Description of the Related Art
Independent Software Vendors (ISV) seek to deliver their services via the SaaS (Software as a Service) business model and further seek to enable existing legacy single-tenant applications to possess multi-tenant features. For these legacy applications, some automation tools (including initialization applications, scripts, etc.) help to initialize their databases (including schema and data). For example, the following is a code segment of an initialization application:
Create table region(regionId long, regionName varchar(50));
Insert into region values(100001, ‘San Francisco’)
. . . (maybe init app is calculating . . . )
Create table salesorder(sold long, merchandiseId long, price long, . . . )
Create table mersalesreport(mdId long, avgprice long)
. . . (maybe init app is calculating . . . )
insert into mersalesreport select merchandiseld, avg(price) from salesorder group by merchandiseld
. . .
The initializing application first crates a table named region, inserts values into this table, creates a table named salesorder and a table named mersalesreport, and takes relevant data from the table salesorder and inserts into the table mersalesreport after calculation.
Initialization tools for legacy applications are generally not applicable to a multi-tenant database. A common multi-tenant database has three types of tables.
The first table is the Application system table. The Application system table's schema is shared among all tenants, and it stores system data shared by all tenants. The privileges of both creating and modifying table schema and table data belong to an application administrator.
The second table is the Tenant shared table. The Tenant shared table's schema is the same for the tenants, and it stores private data of the respective tenants and uses a special tenant ID column to distinguish between data of different tenants. The privileges of creating and modifying table schema belong to an application administrator, while the privileges of creating and modifying table data belong to the respective tenant administrators.
The third table is the Tenant private table. Tenant private table's schema is private to a tenant, and it stores private data of the tenant. The privileges of creating and modifying table schema and table data belong to the respective tenant administrator.
To enable an existing initialization tool for a legacy application applicable to a multi-tenant database, one method is to modify the code of the existing initialization tool, including the step of taking the tenant ID column of a tenant shared table in the multi-tenant database into consideration. An algorithm for such taking is provided as an example below:
modifying the DML (Data Modification Language) in the existing initialization tool:
Insert into salesreport select avg(price) from salesorder into a new DML:                Insert into salesreport select avg(price), tid from salesorder group by tid        
The shortcoming of this method is that large amount of code modification is required, which creates a heavy workload. In addition, the modification is specific to the initialization tool, and thus different modifications are required for different initialization applications. As a result, this method further increases the workload.